﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using LinqToExcel;

namespace FlightService
{
    /// <summary>
    /// Algorithm that looks for the shortest flights
    /// </summary>
    public class DistanceAlgorithm : IAlgorithm
    {
        private Request request;
        private List<Flight> requestedFlights;
        public List<Flight> RequestedFlights
        {
            get { return request.Flights; }
            private set { request.Flights = value; }
        }

        private string selectString
        {
            get
            {
                return "SELECT DateFrom, DateTo, TimeFrom, TimeTo, " +
                       "CityFrom, CityTo, DisplayName as 'Airline', " +
                       "Price, PriceChildren, Adults, Children " +
                       "FROM [dbo].[Flights] F JOIN [dbo].[Airlines] A " +
                       "ON F.Airline_id = A.Id ";
            }
        }

        private string filepath = string.Empty;
        public string Execute(Request request, string filepath)
        {
            this.filepath = filepath;
            requestedFlights = request.Flights;
            this.request = request;
            if (requestedFlights == null)
                return null;

            maxLength = request.IsReturn ? requestedFlights.Count : requestedFlights.Count - 1;
            string response = string.Empty;
            List<Flight> flights = new List<Flight>();

            var excel = new ExcelQueryFactory(filepath);
            string cityFrom = string.Empty;
            DateTime departureDate = DateTime.Now;
            for (int i = 0; i < requestedFlights.Count; i++)
            {
                var flightsSelectedFromFile = from c in excel.Worksheet<Flight>()
                                              where c.CityFrom == requestedFlights[i].CityFrom
                                              select c;

                List<Flight> flightsFiltered = new List<Flight>();

                if (i == 0) // check the FROM flight
                {
                    //flight FROM at DateFrom and to all the cities mentioned
                    cityFrom = requestedFlights[i].CityFrom;
                    departureDate = requestedFlights[i].DateFrom;
                    for (int j = 1; j < requestedFlights.Count; j++)
                    {
                        flightsFiltered.AddRange(flightsSelectedFromFile.Where(row =>
                                                                        row.DateFrom == requestedFlights[i].DateFrom
                                                                        && row.Adults >= request.Adults
                                                                        && row.Children >= request.Children
                                                                        && row.CityFrom == cityFrom
                                                                        && row.CityTo == requestedFlights[j].CityFrom).ToList());
                    }
                }
                else // check the rest of TO flights
                {
                    for (int city = 1; city < requestedFlights.Count; city++)
                    {
                        for (int date = 1; date < requestedFlights.Count - 1; date++)
                        {
                            flightsFiltered.AddRange(flightsSelectedFromFile.Where(row =>
                                                                            row.CityFrom == requestedFlights[i].CityFrom
                                                                            && row.DateFrom == requestedFlights[date].DateFrom
                                                                            && row.CityTo == requestedFlights[city].CityFrom
                                                                            && row.Adults >= request.Adults
                                                                            && row.Children >= request.Children
                                                                            && row.CityTo != requestedFlights[i].CityFrom)
                                                                     .ToList());
                        }
                    }
                    // add return flights
                    if (request.IsReturn)
                    {
                        flightsFiltered.AddRange(flightsSelectedFromFile.Where(row =>
                                                                        row.CityFrom == requestedFlights[i].CityFrom
                                                                        && row.DateFrom == requestedFlights.Last().DateFrom
                                                                        && row.Adults >= request.Adults
                                                                        && row.Children >= request.Children
                                                                        && row.CityTo == cityFrom)
                                                                 .ToList());
                    }
                }
                if (flightsFiltered.Count != 0)
                    flights.AddRange(flightsFiltered);
            }

            flights = runAlgorithm(flights, cityFrom, departureDate);

            if (flights.Any())
            {
                foreach (Flight f in flights)
                {
                    f.DateFrom = new DateTime(f.DateFrom.Year, f.DateFrom.Month, f.DateFrom.Day,
                                              int.Parse(String.Concat(f.TimeFrom[11], f.TimeFrom[12])),
                                              int.Parse(String.Concat(f.TimeFrom[14], f.TimeFrom[15])),
                                              int.Parse(String.Concat(f.TimeFrom[17], f.TimeFrom[18])));
                    f.DateTo = new DateTime(f.DateTo.Year, f.DateTo.Month, f.DateTo.Day,
                                            int.Parse(String.Concat(f.TimeTo[11], f.TimeTo[12])),
                                            int.Parse(String.Concat(f.TimeTo[14], f.TimeTo[15])),
                                            int.Parse(String.Concat(f.TimeTo[17], f.TimeTo[18])));
                }
                foreach (Flight f in flights)
                {
                    f.Adults = request.Adults;
                    f.Children = request.Children;
                }
                response = XmlParser.FlightsToXml(flights);
            }

            return response == string.Empty ? "No flights found" : response;
        }

        public string Execute(Request request, SqlConnection cnn)
        {
            requestedFlights = request.Flights;
            this.request = request;
            if (requestedFlights == null)
                return null;

            maxLength = request.IsReturn ? requestedFlights.Count : requestedFlights.Count - 1;
            var response = string.Empty;
            var flights = new List<Flight>();

            var cityFrom = string.Empty;
            var departureDate = DateTime.Now;
            for (int i = 0; i < requestedFlights.Count; i++)
            {
                var flightsFiltered = new List<Flight>();
                StringBuilder sql;

                if (i == 0) // check the FROM flight
                {
                    //flight FROM at DateFrom and to all the cities mentioned
                    cityFrom = requestedFlights[i].CityFrom;
                    departureDate = requestedFlights[i].DateFrom;
                    for (int j = 1; j < requestedFlights.Count; j++)
                    {
                        sql = new StringBuilder();
                        sql.Append(selectString +
                                   "WHERE CityFrom='" + cityFrom + "' AND " +
                                   "DateFrom='" + requestedFlights[i].DateFrom + "' AND " +
                                   "Adults>=" + request.Adults + " AND " +
                                   "Children>=" + request.Children + " AND " +
                                   "CityTo='" + requestedFlights[j].CityFrom + "'");

                        var cmd = new SqlCommand(sql.ToString(), cnn);
                        var adp = new SqlDataAdapter(cmd);
                        var ds = new DataSet();
                        adp.Fill(ds);

                        var flightsFromDb = Converter.ToFlights(ds.Tables[0]);
                        if (flightsFromDb != null && flightsFromDb.Count > 0)
                            flightsFiltered.AddRange(flightsFromDb);
                    }
                }
                else // check the rest of TO flights
                {
                    for (int city = 1; city < requestedFlights.Count; city++)
                    {
                        for (int date = 1; date < requestedFlights.Count - 1; date++)
                        {
                            sql = new StringBuilder();
                            sql.Append(selectString +
                                       "WHERE CityFrom='" + requestedFlights[i].CityFrom + "' AND " +
                                       "DateFrom='" + requestedFlights[date].DateFrom + "' AND " +
                                       "Adults>=" + request.Adults + " AND " +
                                       "Children>=" + request.Children + " AND " +
                                       "CityTo='" + requestedFlights[city].CityFrom + "' AND " +
                                       "CityTo<>'" + requestedFlights[i].CityFrom + "'");

                            var cmd = new SqlCommand(sql.ToString(), cnn);
                            var adp = new SqlDataAdapter(cmd);
                            var ds = new DataSet();
                            adp.Fill(ds);

                            var flightsFromDb = Converter.ToFlights(ds.Tables[0]);
                            if (flightsFromDb != null && flightsFromDb.Count > 0)
                                flightsFiltered.AddRange(flightsFromDb);
                        }
                    }
                    // add return flights
                    if (request.IsReturn)
                    {
                        sql = new StringBuilder();
                        sql.Append(selectString +
                                   "WHERE CityFrom='" + requestedFlights[i].CityFrom + "' AND " +
                                   "DateFrom='" + requestedFlights.Last().DateFrom + "' AND " +
                                   "Adults>=" + request.Adults + " AND " +
                                   "Children>=" + request.Children + " AND " +
                                   "CityTo='" + cityFrom + "'");

                        var cmd = new SqlCommand(sql.ToString(), cnn);
                        var adp = new SqlDataAdapter(cmd);
                        var ds = new DataSet();
                        adp.Fill(ds);

                        var flightsFromDb = Converter.ToFlights(ds.Tables[0]);
                        if (flightsFromDb != null && flightsFromDb.Count > 0)
                            flightsFiltered.AddRange(flightsFromDb);
                    }
                }
                if (flightsFiltered.Count != 0)
                    flights.AddRange(flightsFiltered);
            }

            flights = runAlgorithm(flights, cityFrom, departureDate);

            if (flights.Any())
            {
                foreach (Flight f in flights)
                {
                    f.DateFrom = new DateTime(f.DateFrom.Year, f.DateFrom.Month, f.DateFrom.Day,
                                          int.Parse(String.Concat(f.TimeFrom[0], f.TimeFrom[1])),
                                          int.Parse(String.Concat(f.TimeFrom[3], f.TimeFrom[4])),
                                          0);
                    f.DateTo = new DateTime(f.DateTo.Year, f.DateTo.Month, f.DateTo.Day,
                                            int.Parse(String.Concat(f.TimeTo[0], f.TimeTo[1])),
                                            int.Parse(String.Concat(f.TimeTo[3], f.TimeTo[4])),
                                            0);
                }
                foreach (Flight f in flights)
                {
                    f.Adults = request.Adults;
                    f.Children = request.Children;
                }
                response = XmlParser.FlightsToXml(flights);
            }

            return response == string.Empty ? "No flights found" : response;
        }

        private List<Flight> bestFlights = new List<Flight>();
        private double bestDistance = double.MaxValue;
        private int maxLength = -1;
        private void checkSubtree(IEnumerable<Flight> flights, List<Flight> currentList, int requestedIndex)
        {
            if (currentList.Count == maxLength)
            {
                var time = new TimeSpan();
                foreach (var f in currentList)
                {
                    DateTime dateFrom;
                    DateTime dateTo;
                    if (filepath == string.Empty)
                    {
                        dateFrom = new DateTime(f.DateFrom.Year, f.DateFrom.Month, f.DateFrom.Day,
                                                    int.Parse(String.Concat(f.TimeFrom[0], f.TimeFrom[1])),
                                                    int.Parse(String.Concat(f.TimeFrom[3], f.TimeFrom[4])),
                                                    0);
                        dateTo = new DateTime(f.DateTo.Year, f.DateTo.Month, f.DateTo.Day,
                                                  int.Parse(String.Concat(f.TimeTo[0], f.TimeTo[1])),
                                                  int.Parse(String.Concat(f.TimeTo[3], f.TimeTo[4])),
                                                  0);
                    }
                    else
                    {
                        dateFrom = new DateTime(f.DateFrom.Year, f.DateFrom.Month, f.DateFrom.Day,
                                              int.Parse(String.Concat(f.TimeFrom[11], f.TimeFrom[12])),
                                              int.Parse(String.Concat(f.TimeFrom[14], f.TimeFrom[15])),
                                              int.Parse(String.Concat(f.TimeFrom[17], f.TimeFrom[18])));
                        dateTo = new DateTime(f.DateTo.Year, f.DateTo.Month, f.DateTo.Day,
                                                int.Parse(String.Concat(f.TimeTo[11], f.TimeTo[12])),
                                                int.Parse(String.Concat(f.TimeTo[14], f.TimeTo[15])),
                                                int.Parse(String.Concat(f.TimeTo[17], f.TimeTo[18])));
                    }
                    time += dateTo - dateFrom;
                }
                //time += int.Parse(f.Price);
                if (time.TotalHours < bestDistance)
                {
                    bestFlights = currentList;
                    bestDistance = time.TotalHours;
                }
            }
            else
            {
                IEnumerable<Flight> neighbors = getNeighbors(flights, currentList.Last(), requestedIndex);
                var properNeighbors = new List<Flight>();
                foreach (Flight f in neighbors)
                    if ((request.IsReturn && currentList.Count == maxLength - 1) || currentList.All(flight => flight.CityFrom != f.CityTo))
                        properNeighbors.Add(f);
                foreach (Flight neighbor in properNeighbors)
                {
                    var temp = new List<Flight>(currentList) {neighbor};
                    checkSubtree(flights, temp, requestedIndex + 1);
                }
            }
        }

        private List<Flight> runAlgorithm(List<Flight> flights, string cityFrom, DateTime departureDate)
        {
            foreach (var t in flights)
            {
                if (t.CityFrom == cityFrom && t.DateFrom == departureDate)
                {
                    checkSubtree(flights, new List<Flight>() { t }, 1);
                }
                else
                    break;
            }
            return bestFlights;
        }

        private IEnumerable<Flight> getNeighbors(IEnumerable<Flight> flights, Flight current, int index)
        {
            var neighbors = flights.Where(flight => flight.CityFrom == current.CityTo
                && flight.DateFrom == requestedFlights[index].DateFrom).ToList();

            return neighbors;
        }
    }
}